package com.supermap.wzhy.module.fr.service;

import com.supermap.wzhy.entity.TMicroIdenmeta;
import com.supermap.wzhy.entity.TMicroTablemeta;
import com.supermap.wzhy.module.data.dao.MicroIdenmetaDao;
import com.supermap.wzhy.module.data.dao.MicroTablemetaDao;
import com.supermap.wzhy.module.fr.dao.FrDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 临时表格工具类
 * Created by sun'fei on 2017-11-01.
 */
@Service
public class TableTempService {

    @Autowired
    private FrDao frDao;

    @Autowired
    private MicroIdenmetaDao microIdenmetaDao;

    @Autowired
    private MicroTablemetaDao microTablemetaDao;


    public static String old_table = "yzym_an_baseinfo";
    public static String new_table = "yzym_an_baseinfo_ls";

    //informix sql执行语句
    public static String search_sql = "select count(*) from systables where tabname = ?";
    public static String delete_sql = "delete from "+new_table;
    public static String drop_sql = "drop from ?";
    public static String insert_sql = "INSERT INTO "+new_table+" SELECT * FROM "+old_table;
    public static String temp_sql = "SELECT * FROM "+old_table+" INTO temp "+new_table;


    /**
     * 数据快照表-临时表
     * @param table_name 快照表名
     * @return 创建结果
     */
    public boolean createTableTemp(String table_name,String new_table){
        boolean boo ;
        String count = frDao.query(search_sql,new_table).get(0).toString();
        if ("0".equals(count)){
            //dropTableTemp(new_table);
           createTempTable(new_table);

        }
        frDao.execute(delete_sql);

        //插入数据
        frDao.execute(insert_sql);
        boo = true;
        System.out.println("数据快照表 " + new_table + "创建成功！");
        return boo;
    }

    /**
     * 清空表失败
     * @param table_name  表名
     * @return   执行结果
     */
    public boolean deleteTableTemp(String table_name){
        boolean boo = false;
        try {
            frDao.execute(delete_sql,table_name);
            boo = true;
        }catch (Exception e){
            System.out.println("清空 " + table_name + " 失败");
            return boo;
        }
        return boo;
    }


    /**
     * 删除表失败
     * @param table_name  表名
     * @return   执行结果
     */
    public boolean dropTableTemp(String table_name){
        boolean boo = false;
        try {
            frDao.execute(drop_sql,table_name);
            boo = true;
        }catch (Exception e){
            System.out.println("删除 " + table_name + " 失败");
            return boo;
        }
        return boo;
    }


    public boolean createTempTable(String new_table){
        String create = "create table "+new_table+" (" +
                "ancheid varchar(50)," +
                "anchedate date," +
                "ancheyear char(4)," +
                "regno char(20)," +
                "uniscid char(18)," +
                "entname varchar(255)," +
                "tel varchar(50)," +
                "addr lvarchar(512)," +
                "postalcode char(30)," +
                "email varchar(255)," +
                "busst char(30)," +
                "empnum decimal," +
                "assgro decimal," +
                "liagro decimal," +
                "vendinc decimal," +
                "maibusinc decimal," +
                "progro decimal," +
                "netinc decimal," +
                "ratgro decimal," +
                "totequ decimal," +
                "haswebsite char(30)," +
                "hastransam char(40)," +
                "s_ext_timestamp datetime year to second" +
                ")";

        frDao.execute(create);
        System.out.println("快照表建立。。。。");

        /*String key = "ALTER TABLE "+new_table+" ADD PRIMARY KEY (ancheid)";
        frDao.execute(key);
        System.out.println("主键建立。。。。");*/

        String index = "CREATE INDEX year_temp_1 ON "+new_table+" (ancheid,s_ext_timestamp)";
        frDao.execute(index);
        System.out.println("索引建立。。。。");

        return true;
    }



    /**
     * 获取查询指标字符串
     *
     * @param idenmetas
     * @return
     */
    public String getQueryFields(List<TMicroIdenmeta> idenmetas) {
        StringBuilder b = new StringBuilder();
        StringBuilder c = new StringBuilder();
        int index = 0;
        int size = idenmetas.size();
        for (TMicroIdenmeta i : idenmetas) {
            //informix char类型转换
            if (i.getIdenType() == 1 && i.getIdenLength() <= 255) {
                c.append(i.getIdenCode().toUpperCase() + " varchar(255)" );
            } else if (i.getIdenType() == 1 && i.getIdenLength() > 255 && i.getIdenName() != null) {
                c.append(i.getIdenCode().toUpperCase() + " lvarchar(3000)" );
            }else if(i.getIdenType() == 3){
                c.append(i.getIdenCode().toUpperCase() + " date" );
            }else if (i.getIdenType() == 2){
                c.append(i.getIdenCode().toUpperCase() + " decimal");
            }else {
                c.append(i.getIdenCode().toUpperCase() + " varchar("+i.getIdenLength()+")" );
            }

            if (index < size - 1) {
                c.append(",");
            }
            index++;
        }
        return c.toString();
    }

}
